﻿using System;
using System.Data;

namespace org.se.fs.model.Table
{
    public class Institution : DataContext
    {
        public DataTable GetInstitutionLists()
        {
            using (var databaseManager = GetDatabase())
            {
                databaseManager.Open();
                var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "GetInstitutionLists");
                return ToDataTable(dataSet);
            }
        }

        public DataTable GetInstitutionActive()
        {
            using (var databaseManager = GetDatabase())
            {
                databaseManager.Open();
                var dataSet = databaseManager.ExecuteDataSet(CommandType.StoredProcedure, "GetInstitutionActive");
                return ToDataTable(dataSet);
            }
        }

        public UInt64 Insert(string name, string code, UInt64 branchId, UInt64 userCreateId, string address, string contactEmail, string contactMobileNum, string contactName, string contactPosition, string contactTelNum, string faxNum, string mobileNum, string telNum)
        {
            using (var databaseManager = GetDatabase())
            {
                databaseManager.Open();
                databaseManager.CreateParameters(13);
                databaseManager.AddParameters(0, "name", name);
                databaseManager.AddParameters(1, "code", code);
                databaseManager.AddParameters(2, "branch_id", branchId);
                databaseManager.AddParameters(3, "user_create_id", userCreateId);
                databaseManager.AddParameters(4, "address",address );
                databaseManager.AddParameters(5, "contact_email", contactEmail);
                databaseManager.AddParameters(6, "contact_mobile_num", contactMobileNum);
                databaseManager.AddParameters(7, "contact_name", contactName);
                databaseManager.AddParameters(8, "contact_position", contactPosition);
                databaseManager.AddParameters(9, "contact_tel_num", contactTelNum);
                databaseManager.AddParameters(10, "fax_num", faxNum);
                databaseManager.AddParameters(11, "mobile_num", mobileNum);
                databaseManager.AddParameters(12, "tel_num", telNum);
                var result = databaseManager.ExecuteScalar(CommandType.StoredProcedure, "InsertInstitution");
                return Convert.ToUInt64(result);
            }
        }

        public bool Update(UInt64 id, string name, string code, UInt64 branchId, UInt64 userCreateId, string address, string contactEmail, string contactMobileNum, string contactName, string contactPosition, string contactTelNum, string faxNum, string mobileNum, string telNum)
        {
            using (var databaseManager = GetDatabase())
            {
                databaseManager.Open();
                databaseManager.CreateParameters(14);
                databaseManager.AddParameters(0, "id", id);
                databaseManager.AddParameters(1, "name", name);
                databaseManager.AddParameters(2, "code", code);
                databaseManager.AddParameters(3, "branch_id", branchId);
                databaseManager.AddParameters(4, "user_create_id", userCreateId);
                databaseManager.AddParameters(5, "address", address);
                databaseManager.AddParameters(6, "contact_email", contactEmail);
                databaseManager.AddParameters(7, "contact_mobile_num", contactMobileNum);
                databaseManager.AddParameters(8, "contact_name", contactName);
                databaseManager.AddParameters(9, "contact_position", contactPosition);
                databaseManager.AddParameters(10, "contact_tel_num", contactTelNum);
                databaseManager.AddParameters(11, "fax_num", faxNum);
                databaseManager.AddParameters(12, "mobile_num", mobileNum);
                databaseManager.AddParameters(13, "tel_num", telNum);
                var result = databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "UpdateInstitution");
                return Convert.ToBoolean(result);
            }
        }

        public bool Delete(UInt64 uid)
        {
            using (var databaseManager = GetDatabase())
            {
                databaseManager.Open();
                databaseManager.CreateParameters(1);
                databaseManager.AddParameters(0, "id", uid);
                var result = databaseManager.ExecuteNonQuery(CommandType.StoredProcedure, "DeleteInstitution");
                return Convert.ToBoolean(result);
            }
        }
    }
}
